﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Models;

namespace DAL {
    public class FriService {
        private static string ConServerStr = @" Data Source=bds249611391.my3w.com;Initial Catalog=bds249611391_db;Persist Security Info=True;User ID=bds249611391;Password=hq312453";
        SqlConnection conn = new SqlConnection(ConServerStr);

        private void Open() {
            if (conn.State == System.Data.ConnectionState.Closed)
                conn.Open();
        }


        /// <summary>
        /// 查询所有好友
        /// </summary>
        /// <param name="LoginName">子账户名称</param>
        /// <param name="ChildName">微信名称</param>
        /// <returns></returns>
        public List<Fri> SearchAll( string LoginName, string ChildName ) {
            string sql = string.Format("select * from {0}_微信好友表 where nike_name='{1}'", LoginName, ChildName);
            List<Fri> FriList = new List<Fri>();
            SqlCommand com = new SqlCommand(sql, conn);
            Open();
            SqlDataReader dr = com.ExecuteReader();
            while (dr.Read()) {
                Fri f = new Fri();
                f.nick_name = dr["nike_name"].ToString();
                f.wx_name = dr["wx_name"].ToString();
                f.weixin = dr["weixin"].ToString();
                f.Sex = dr["Sex"].ToString();
                f.Province = dr["Province"] == System.DBNull.Value ? "" : dr["Province"].ToString();
                FriList.Add(f);
            }
            dr.Close();
            com.Dispose();
            conn.Close();
            return FriList;
        }


        //新增朋友数量
        public string SearchNewFriend( string ChildName ) {
            string sql = string.Format("select count(1) from ChatLog{0} where Readed=1 and Type=37", ChildName);
            SqlCommand com = new SqlCommand(sql, conn);
            Open();
            string str = com.ExecuteScalar().ToString();
            com.Dispose();
            conn.Close();
            return str;
        }


        //查询好友地区
        public Dictionary<string, int> SearchFriendProvince( string LoginName ) {
            Dictionary<string, int> dic = new Dictionary<string, int>();
            string sql = string.Format("select COUNT(Province) as ProNum,Province from {0}_微信好友表 where Sex != 0 group by Province", LoginName);
            SqlCommand com = new SqlCommand(sql, conn);
            Open();
            SqlDataReader dr = com.ExecuteReader();
            while (dr.Read()) {
                string str = dr["Province"].ToString() == string.Empty ? "未知" : dr["Province"].ToString();
                dic.Add(str, Convert.ToInt32(dr["ProNum"]));
            }
            dr.Close();
            com.Dispose();
            conn.Close();
            return dic;
        }
    }
}
